Re: [GENERAL] Alphabetical sorting... - Mailing list pgsql-general

At 5:20 PM 7/6/98, Chris Johnson wrote:
>I don't know if the code given in the reply (THANK YOU PAUL!!!!) works for
>6.3.2 (I'm downloading now to test it), but it failed in 6.3 with the
>error 'ERROR:  parser: parse error at or near "("' - but it put me on the
>right track.

It seems unlikely that functions called on where clause elements would be
added in a minor release change from 6.3 to 6.3.2, but what do I know?

>I wound up using the following:
>   select test, lower(test) as ltest from testtable order by ltest;
>
>which *almost* gets it, but is close enough for me...
>
>test |ltest
>-----+-----
>a    |a
>A    |a
>ABLE |able
>Able |able
>AXIOM|axiom
>
>- notice that 'a' and 'A' are reversed... there was a 50/50 shot at it
>being perfect, but that will happen in so few cases as to be irrelevant
>(hopefully nobody will notice ;-)

Odds are really good that you've figured this out by now, but...

select test, lower(test) as ltest from testtable order by ltest;

Says sort by lowercase of test, so here's how the computer thinks:

Change 'A' into 'a'.
Change 'a' into 'a'. [Not much of a change, but that's a computer for ya]
'a' == 'a'.
Hmmm. Two rows, same thing, exactly equal, no difference in ordering.
I'll just spit 'em out in whichever order they are now.

Change 'Able' into 'able'.
Change 'ABLE' into 'able'.
'able' == 'able'
Hmmm. Two rows, same thing, exactly equal, no difference in ordering.
I'll just spit 'em out in whichever order they are now.

So you do, in fact, have a 50/50 chance of it coming up either way.
Actually, technically, you have an indeterminant chance, since SQL specs
allows the designers to do whatever the hell they please in this instance.
Most likely, they just won't do anything, and the stuff will spit out in
the order it happens to be in the database, which is probably mostly in the
order in which you entered it, unless something else got deleted right
before one of these guys got inserted, and one of these guys took that
empty spot.  Ya follow that?  Anyway, it will come out whichever way it
happens to end up from its starting point and the sort routine.  However,
there is no guarantee that PostgreSQL will do tomorrow what it happens to
be doing today.  For instance, in version 7, they could hire a demon from
the 7th level of hell to decide which to give you first, if they so
desired. :-)

Believe it or not, I'm not completely wasting your time.  I just thought it
important that you understand why things were coming out the way they were
before I told you how to get what you want.  Guess 'cuz I used to teach.
Anyway, you could do:

select test, lower(test) as ltest from testtable order by ltest, test;
to get the 'A' row first, or

select test, lower(test) as ltest from testtable order by ltest, test desc;
to get the 'a' row first.

Now you are clearly stating you want it to sort first by lower-case, and,
in case of a tie, sort by test.

[Is it descending or desc?   Maybe DSC.  Whatever.  RTFM]

The point being that once you tell it to lowercase it, and then to sort by
that, you can hardly expect it to remember that it was uppercase before you
lowercased it unless you ask it to.  It's just a stupid computer, after
all. :-)

Whew.  Maybe this belonged on novice.  Sorry folks, but I answered it where
it got posted.  :-^

--
--
-- "TANSTAAFL" Rich lynch@lscorp.com